from CoTec.core.database.mysql_go import MysqlHelper
from CoTec.utility.string.string_go import StringHelper
from CoTec.utility.string.string_go import StringHelper as SH
from CoTec.core.exception.exception_go import ExceptionGo as E
from CoTec.utility.string.string_go import genUUID

import abc

class GeneralDao():
    """基础数据库操作封装"""

    def __init__(self, conf):
        self.config = conf
        self.mysql = MysqlHelper(conf)
        # pass

    # @abc.abstractmethod
    # def save(self, id:str, dic:dict):
    #     pass

    @abc.abstractmethod
    def save(self,  dic: dict, id: str=None):
        pass

    #如果以 <!> 开头 使用 '!='
    @staticmethod
    def dic_to_sql_part(dic):
        res = ''

        for key,value in dic.items():
            #tuple 组合拆分
            if isinstance(value,tuple):
                for item in value:
                    res = GeneralDao.dic_to_sql_loop_part(res, key, item)
            else:
                res = GeneralDao.dic_to_sql_loop_part(res, key, value)

        res = SH.cut_tail(res, 'and')
        # res += '1=1'

        return res

    @staticmethod
    def dic_to_sql_loop_part(res, key, value, prefix=None, noAnd=False):
        """
        ck = '<!>'
        like = '<L>'
        not_like = '<NL>'
        go = '<BE>'
        free = '<FREE>'
        :param res:
        :param key:
        :param value:
        :return:
        """

        if prefix is not None:
            _key = str(prefix) + '.'
            if '>' in key:
                head = SH.extra_a_to_b(key, '<', '>')
                key = SH.cut_head(key, head)
                key = head + str(prefix) + '.' + key
            else:
                key = str(prefix) + '.' + key
        else:
            _key = ''

        ck = '<!>'
        like = '<L>'
        not_like = '<NL>'
        go = '<BE>'
        free = '<FREE>'
        tin = '<IN>'

        if key.startswith(ck):
            _key = StringHelper.cut_head(key, ck)

            res += _key
            res += ' != "'
        elif key.startswith(like):
            _key = StringHelper.cut_head(key, like)

            res += _key
            res += ' like "'
        elif key.startswith(not_like):
            _key = StringHelper.cut_head(key, not_like)

            res += _key
            res += ' not like "'
        elif key.startswith(go):
            _key = StringHelper.cut_head(key, go)

            res += _key
            res += ' >= "'
        elif key.startswith(tin):
            _key = StringHelper.cut_head(key, tin)

            res += _key
            res += ' in ('
        elif key.startswith(free):
            if prefix is not None:
                res += ' ' + prefix + '.' + str(value) + ' '
            else:
                res += ' ' + str(value) + ' '
        else:
            res += key
            res += ' = "'

        if key.startswith(free):
            pass
        elif key.startswith(tin):
            res += GeneralDao.build_sql(str(value[0]), [_key], value[1]) + ')'
        else:
            res += str(value) + '"'

        if noAnd:
            res += ' '
        else:
            res += ' and '

        return res

    @staticmethod
    def dic_to_sql_start_filter(key, value):
        res = ''
        res += key
        res += ' '
        res += value
        res += '"'

        return res

    @staticmethod
    def list_to_sql_part(list):
        res = ''
        for item in list:
            res += '`' + item + '`'
            res += ','

        res = res[0: len(res) - 1]

        return res

    @staticmethod
    def part_dic_to_update_sql(dic):
        sql = ''
        for key in dic:
            if key != 'id':
                sql += key
                sql += ' = \''
                sql += str(dic[key])
                sql += '\','

        sql = sql[0: len(sql) - 1]
        return sql

    @staticmethod
    def part_dic_to_update_sql_raw(dic):
        sql = ''
        for key in dic:
            if key != 'id':
                sql += key
                sql += ' = '
                sql += str(dic[key])
                sql += ','

        sql = sql[0: len(sql) - 1]
        return sql

    # def get_list(self, table_name, select_list, where_dic=None, limit_tup=None, order_tup=None, group_str=None):
    #     select_sql = GeneralDao.list_to_sql_part(select_list)
    #
    #     sql = 'select ' + select_sql + ' from ' + table_name
    #
    #     if where_dic is not None:
    #         where_sql = GeneralDao.dic_to_sql_part(where_dic)
    #         sql += ' where ' + where_sql
    #
    #     if group_str is not None:
    #         sql += ' group by ' + group_str
    #
    #     if order_tup is not None:
    #         sql += ' order by ' + order_tup[0] + ' ' + order_tup[1]
    #
    #     if limit_tup is not None:
    #         sql += ' limit ' + str(limit_tup[0]) + ',' + str(limit_tup[1])
    #
    #     # sql = self.build_sql(table_name,select_list,where_dic,limit_tup,order_tup,group_str)
    #
    #     mysql = MysqlHelper(self.config)
    #     result = mysql.load(sql)
    #
    #     return result

    def get_list(self, table_name, select_list, where_dic=None, limit_tup=None, order_tup=None, group_str=None):
        select_sql = GeneralDao.list_to_sql_part(select_list)

        sql = 'select ' + select_sql + ' from ' + table_name

        if where_dic is not None:
            where_sql = GeneralDao.dic_to_sql_part(where_dic)
            sql += ' where ' + where_sql

        if group_str is not None:
            sql += ' group by ' + group_str

        if order_tup is not None:
            sql += ' order by ' + order_tup[0] + ' ' + order_tup[1]

        if limit_tup is not None:
            sql += ' limit ' + str(limit_tup[0]) + ',' + str(limit_tup[1])

        # sql = self.build_sql(table_name,select_list,where_dic,limit_tup,order_tup,group_str)

        mysql = MysqlHelper(self.config)
        result = mysql.load(sql)

        return result

    def get_by_sqls(self, select_sql_str, from_sql_str, where_sql_str=None, limit_tup=None, order_tup=None, group_str=None):
        select_sql = select_sql_str

        sql = 'select ' + select_sql + ' from ' + from_sql_str

        if where_sql_str is not None:
            sql += ' where ' + where_sql_str

        if group_str is not None:
            sql += ' group by ' + group_str

        if order_tup is not None:
            sql += ' order by ' + str(order_tup[0]) + ' ' + str(order_tup[1])

        if limit_tup is not None:
            sql += ' limit ' + str(limit_tup[0]) + ',' + str(limit_tup[1])

        mysql = MysqlHelper(self.config)
        result = mysql.load(sql)

        return result

    @staticmethod
    def build_sql(table_name, select_list, where_dic=None, limit_tup=None, order_tup=None, group_str=None):
        select_sql = GeneralDao.list_to_sql_part(select_list)

        # mysql = MysqlHelper(self.config)

        sql = 'select ' + select_sql + ' from ' + table_name

        if where_dic is not None:
            where_sql = GeneralDao.dic_to_sql_part(where_dic)
            sql += ' where ' + where_sql

        if group_str is not None:
            sql += ' group by ' + group_str

        if order_tup is not None:
            sql += ' order by ' + order_tup[0] + ' ' + order_tup[1]

        if limit_tup is not None:
            sql += ' limit ' + str(limit_tup[0]) + ',' + str(limit_tup[1])

        # result = mysql.load(sql)

        return sql

    # def get(self,table_name, select_list,where=None,limit=None,order=None,group=None):
    #     """
    #     获取列表 如果获取的 list  只有一个，那么返回 list 否者返回 dic
    #     :param table_name:
    #     :param select_list:
    #     :param where:
    #     :param limit:
    #     :param order:
    #     :param group:
    #     :return:
    #     """
    #     val = self.get_list(table_name, select_list, where_dic=where, limit_tup=limit, order_tup=order, group_str=group)
    #
    #     if len(select_list) == 1:
    #         result = []
    #         for item in val:
    #             result.append(item[select_list[0]])
    #
    #         return result
    #
    #     return val

    # def get_list(self,table_name, select_list,where=None,limit=None,order=None,group=None,ex=False):
    #     """
    #     获取列表 如果获取的 list  只有一个，那么返回 list 否者返回 dic
    #     :param table_name:
    #     :param select_list:
    #     :param where:
    #     :param limit:
    #     :param order:
    #     :param group:
    #     :return:
    #     """
    #     val = self.get_list(table_name, select_list, where_dic=where, limit_tup=limit, order_tup=order, group_str=group)
    #
    #     if len(select_list) == 1:
    #         result = []
    #         for item in val:
    #             result.append(item[select_list[0]])
    #
    #         if ex and limit is not None:
    #             if limit[1] == 1:
    #                 return result[0]
    #
    #         return result
    #
    #     return val


    def get(self,table_name, select_list,where=None,limit=None,order=None,group=None,ex=False):
        """
        获取列表 如果获取的 list  只有一个，那么返回 list 否者返回 dic
        :param table_name:
        :param select_list:
        :param where:
        :param limit:
        :param order:
        :param group:
        :return:
        """
        val = self.get_list(table_name, select_list, where_dic=where, limit_tup=limit, order_tup=order, group_str=group)

        if len(select_list) == 1:
            result = []
            for item in val:
                result.append(item[select_list[0]])

            if ex and limit is not None:
                if limit[1] == 1:
                    return result[0]

            return result

        return val

    def com_get(self, com_list, combine_key=None, limit_tuple=None):
        last_nick = None

        sql_select = ' '
        sql_from = ' '
        sql_where = ' '
        sql_and = ' '
        sql_limit = ' '
        sql_order = ' order by '

        for index, com in enumerate(com_list):
            nick = 'n' + str(index)

            table_name = com[0]
            select_list = com[1]
            # where_tuple_list = com[2]
            where_dic = com[2]
            order_tuple = com[3]

            sql_from += table_name + ' as ' + nick + ','

            for select in select_list:
                sql_select += nick + '.' + select + ','

            # if where_dic is not None:
            #     for key, value in where_dic.values():
            #         sql_where += nick + '.' + where_tuple[0] + ' = "' + where_tuple[1] + '"' + ' and '
            if where_dic is not None:
                for key, value in where_dic.items():
                    # sql_where += nick + '.' + where_tuple[0] + ' = "' + where_tuple[1] + '"' + ' and '
                    # sql_where += nick + '.' + where_tuple[0] + ' = "' + where_tuple[1] + '"' + ' and '
                    sql_where = GeneralDao.dic_to_sql_loop_part(sql_where, key, value, prefix=nick)

            if combine_key is not None:
                if last_nick is not None:
                    sql_and += last_nick + '.' + combine_key + ' = ' + nick + '.' + combine_key + ' and '

            if order_tuple is not None:
                sql_order += nick + '.' + order_tuple[0] + ' ' + order_tuple[1] + ' '

            last_nick = nick

        if combine_key is not None:
            sql_where += sql_and

        if limit_tuple is not None:
            sql_limit += ' limit ' + str(limit_tuple[0]) + ',' + str(limit_tuple[1])

        sql_where = SH.cut_tail(sql_where, 'and')
        sql_select = SH.cut_tail(sql_select, ',')
        sql_from = SH.cut_tail(sql_from, ',')

        sql = 'select ' + sql_select + ' from ' + sql_from + ' where ' + sql_where + sql_order + sql_limit

        mysql = MysqlHelper(self.config)
        result = mysql.load(sql)

        select_count = 0
        the_select = ''

        for select in com_list:
            if len(select[1]) == 1:
                the_select = select[1][0]
            select_count += len(select[1])

        if select_count == 1:
            res = self.dic_to_key_list(result, the_select)
            return res

        return result

    def dic_to_key_list(self, dic_list, key):
        _result = []
        for item in dic_list:
            _result.append(item[key])
        return _result

    def insert_or_update(self, dic: dict, table_name: str, id: str=None):
        """
        根据 id 来 update 或 insert
        :param dic:
        :param table_name:
        :param id:
        :return:
        """
        update_key = 'id'

        #判断 update 还是 insert
        if update_key in dic.keys():
            where_dic = {update_key:dic[update_key]}
            dic.pop(update_key)

            return self.update_with_dic(table_name, dic, where_dic)
        else:
            return self.insert_ex(dic, table_name, id)

    def insert_ex(self, dic: dict, table_name: str, id: str=None):
        """
        update 或 insert
        :param dic:
        :param table_name:
        :param id:
        :return:
        """
        if id is None:
            id = genUUID()

        mysql = MysqlHelper(self.config)

        _sql = "INSERT INTO " + table_name + "(`id`, "
        _val = " VALUES (%s,"
        _par = [id, ]

        for key in dic:
            _sql += "`"
            _sql += key
            _sql += "`,"

            if type(dic[key]) == type(str):
                _val += "%s,"
            elif type(dic[key]) == type(int):
                _val += "%i,"
            else:
                _val += "%s,"

            _par.append(dic[key])

        _sql = _sql[0: len(_sql) - 1]
        _sql += ")"

        _val = _val[0: len(_val) - 1]
        _val += ")"

        _sql += _val

        mysql.save(_sql, _par)

    def insert(self, table_name, id, dic):
        self.insert_ex(dic, table_name, id)

    # def insert(self, table_name, id, dic):
    # def insert_ex(self, dic:dict, table_name:str, id:str=None):
    #     if id is None:
    #         id = genUUID()
    #
    #     mysql = MysqlHelper(self.config)
    #
    #     _sql = "INSERT INTO " + table_name + "(`id`, "
    #     _val = " VALUES (%s,"
    #     _par = [id, ]
    #
    #     for key in dic:
    #         _sql += "`"
    #         _sql += key
    #         _sql += "`,"
    #
    #         if type(dic[key]) == type(str):
    #             _val += "%s,"
    #         elif type(dic[key]) == type(int):
    #             _val += "%i,"
    #         else:
    #             _val += "%s,"
    #
    #         _par.append(dic[key])
    #
    #     _sql = _sql[0: len(_sql) - 1]
    #     _sql += ")"
    #
    #     _val = _val[0: len(_val) - 1]
    #     _val += ")"
    #
    #     _sql += _val
    #
    #     val = mysql.save(_sql, _par)
    #     return val

        # sql = "insert into "
        # sql += article_table_name
        # sql += " set id=%s"
        # mysql.save(sql, id)

    def add_column(self, table_name, column_str, type_str, default_str=None, after_str=None, comment_str=None):
        """
        添加列
        :param table_name:
        :param column_str:
        :param type_str:
        :param after_str:
        :return:
        """
        mysql = MysqlHelper(self.config)

        if default_str is None:
            default_str = 'NULL'

        if after_str is None:
            after_str = ''
        else:
            after_str = 'AFTER ' + after_str + ' '

        sql = 'ALTER TABLE ' + table_name + ' ADD COLUMN ' + column_str + ' ' + type_str + ' ' + default_str + ' '

        if comment_str is not None:
            sql += 'COMMENT ' + str(comment_str) + ' '

        sql += after_str

        sta = mysql.update(sql)
        return sta

    def add_table(self, table_name, primary_key, primary_type, primary_increase:int = None):
        """
        添加表
        :param table_name:
        :param primary_key:
        :param primary_type:
        :return:
        """
        mysql = MysqlHelper(self.config)

        sql = 'CREATE TABLE ' + table_name + ' ( ' + primary_key + ' ' + primary_type + ' NOT NULL '

        if primary_increase is not None:
            sql += 'AUTO_INCREMENT '

        sql += ', PRIMARY KEY (' + primary_key + ')) '

        if primary_increase is not None:
            sql += ' AUTO_INCREMENT=' + str(primary_increase) + ' ;'
        else:
            sql += ';'

        sta = mysql.update(sql)
        return sta

    def update_with_dic(self, table_name, update_dic, where_dic, raw=None):
        mysql = MysqlHelper(self.config)

        if raw is not None:
            update_sql = GeneralDao.part_dic_to_update_sql_raw(update_dic)
        else:
            update_sql = GeneralDao.part_dic_to_update_sql(update_dic)

        where_sql = GeneralDao.dic_to_sql_part(where_dic)

        sql = "update " + table_name + " set " + update_sql + " where " + where_sql

        sta = mysql.update(sql)
        return sta

    def execute(self, sql):
        mysql = MysqlHelper(self.config)

        sta = mysql.update(sql)
        return sta

    def load(self, sql):
        mysql = MysqlHelper(self.config)

        sta = mysql.load(sql)
        return sta

    def com_update(self, com_list, combine_key=None):
        last_nick = None

        sql_set = ' '
        sql_from = ' '
        sql_where = ' '
        sql_and = ' '
        sql_limit = ' '

        for index, com in enumerate(com_list):
            nick = 'n' + str(index)

            table_name = com[0]
            set_tuple_list = com[1]
            where_dic = com[2]

            sql_from += table_name + ' as ' + nick + ','

            for set_tuple in set_tuple_list:
                sql_set += nick + '.' + set_tuple[0] + ' = "' + set_tuple[1] + '"' + ','

            if where_dic is not None:
                sql_where += GeneralDao.dic_to_sql_part(where_dic)

            if combine_key is not None:
                if last_nick is not None:
                    sql_and += last_nick + '.' + combine_key + ' = ' + nick + '.' + combine_key + ' and '

            last_nick = nick

        if combine_key is not None:
            sql_where += sql_and

        # sql_where = SH.cut_tail(sql_where, 'and')
        sql_set = SH.cut_tail(sql_set, ',')
        sql_from = SH.cut_tail(sql_from, ',')

        sql = 'update ' + sql_from + ' set ' + sql_set + ' where ' + sql_where + sql_limit

        mysql = MysqlHelper(self.config)
        result = mysql.load(sql)

        return result

    def readyList(self, dic_list, key):
        """
        把 dictionary 提取出 一个 以 key 为关键字的 list
        :param dic_list:
        :param key:
        :return:
        """
        list = []

        try:
            for item in dic_list:
                list.append(item[key])
        except Exception as e:
            E.out_err(e)

        return list


# table_name = 'add_mp_list'
# select_list = ['id', 'type', 'key', 'extra0', 'last_access_date', 'valid']
# where_dic = {'id':'f493ugh943hg'}

# general = GeneralDao()
# general.get_list(table_name,select_list,limit_tup=(0,5),where_dic=where_dic)

# general.add_column(table_name,'www','varchar(255)')